---
title: Get SQL Server connection string
summary: Returns a validated, cached SQL Server connection string composed from an Environment API template and a secure credential.
status: draft
owner: Architecture
last_reviewed: 2026-10-09
operation_id: OP-01
trigger: GET /v1/Connections/SQL/{server}/{instance}/{username}
---

# OP-01: Get SQL Server connection string

This operation composes a SQL Server connection string for the requested server, instance and user, proves that it connects by running a constant probe query, caches it for the lifetime of the process, and returns it to the caller. A repeat request for the same key still reads the Environment API template, but is then answered from the cache without retrieving the secure credential or contacting the database.

| Field | Value |
| --- | --- |
| **Operation ID** | OP-01 |
| **Trigger** | HTTP request from an authorized caller |
| **Entry endpoint** | [`GET /v1/Connections/SQL/{server}/{instance}/{username}`](../endpoints/connections.md#get-v1-connections-sql-server-instance-username) |
| **Owning component** | `ConnectionsController.GetSqlConnectionString`, delegating to `ConnectionManager.GetSqlConnectionString` |
| **Inputs** | Route values `server`, `instance`, `username` (inferred route binding); Environment API template `sqlconnectiontemplate` and a secure credential for the composed key |
| **Outputs** | `ActionResult<string>` holding the credential-bearing connection string |
| **Failure response** | 404 for a missing secure setting, 500 for probe or credential failures, produced from `ConnectionManagerException` by package middleware† (see [Error handling](#error-handling)) |

## Steps

1. Authorize the caller against resource `DBConnAPIRetrieveResource` ([BR-DBC-025](../business-rules.md#br-dbc-025-resource-authorization-on-every-endpoint))
   - On failure: a denial produced by the package authorization handler†; the exact status is not visible in source**
2. Write the audit log entry with the key parts, the caller's `UserId`, the `AuthenticationScheme` and the time ([BR-DBC-022](../business-rules.md#br-dbc-022-retrieval-requests-are-audit-logged))
3. Build the cache key `SQL.<server>.<instance>.<user>`, which is case-sensitive ([BR-DBC-002](../business-rules.md#br-dbc-002-cache-key-format))
4. Build the lower-cased secure-setting key `sql_<server>_<instance>_<user>` ([BR-DBC-005](../business-rules.md#br-dbc-005-secure-setting-keys-are-lower-cased))
5. Read the template `sqlconnectiontemplate` from group `dbconnectionapi` on every request, before the cache check, and replace `{SERVER}` and `{INSTANCE}` ([BR-DBC-007](../business-rules.md#br-dbc-007-templates-come-from-the-environment-api-group))
   - A missing template raises an unhandled null reference; the status is not visible in source**
6. Look the key up in the in-memory cache ([BR-DBC-001](../business-rules.md#br-dbc-001-serve-from-the-cache-when-the-key-is-present))
   - On a hit: return the cached string with status 200 and stop. Only the secure-credential retrieval, validation, the probe and cache insertion are skipped; the template lookup in step 5 already happened
7. On a miss, check that the secure setting exists ([BR-DBC-009](../business-rules.md#br-dbc-009-missing-secure-setting-returns-404))
   - On failure: `ConnectionManagerException` with status 404, rendered by package middleware†
8. Verify the backoff configuration ([BR-DBC-011](../business-rules.md#br-dbc-011-backoff-list-must-cover-the-retry-attempts))
9. Decrypt the secure setting and split it at the first pipe into user and password ([BR-DBC-010](../business-rules.md#br-dbc-010-credential-split-at-the-first-pipe%2C-unguarded))
   - A missing pipe raises `ArgumentOutOfRangeException` outside the retry loop; the status is not visible in source**
10. Substitute `{USER}` and `{PW}` into the template ([BR-DBC-008](../business-rules.md#br-dbc-008-credentials-are-substituted-into-the-template))
11. Probe the composed string, up to four attempts with 2 s, 3 s and 5 s waits ([BR-DBC-012](../business-rules.md#br-dbc-012-four-probe-attempts-with-2%2C-3-and-5-second-backoff))
    - Each attempt decodes the legacy token for the string under test only ([BR-DBC-019](../business-rules.md#br-dbc-019-legacy-token-decoded-for-the-probe-only))
    - The probe opens a unit of work through `IUnitOfWorkFactory` and runs `SELECT 1`; a non-null result means valid ([BR-DBC-018](../business-rules.md#br-dbc-018-probe-validity-definition))
    - A false result counts as a failure with status 500 ([BR-DBC-014](../business-rules.md#br-dbc-014-a-false-probe-result-is-a-failure))
    - Permanent errors (for example `Login failed`) stop the loop at once ([BR-DBC-013](../business-rules.md#br-dbc-013-permanent-errors-stop-retrying))
12. After the final failure, map the error to a status ([BR-DBC-016](../business-rules.md#br-dbc-016-other-final-failures-map-to-500), [BR-DBC-017](../business-rules.md#br-dbc-017-an-existing-typed-exception-keeps-its-status))
    - A typed exception keeps its status; a TNS alias error maps to 400 ([BR-DBC-015](../business-rules.md#br-dbc-015-tns-alias-errors-map-to-400)) and is possible here only by inference*
13. On success, log the validation and add the string to the cache under the lock ([BR-DBC-003](../business-rules.md#br-dbc-003-guarded-cache-insert%2C-first-writer-wins), [BR-DBC-004](../business-rules.md#br-dbc-004-cache-entries-never-expire))
14. Return the connection string with status 200

> **Origin**: Steps 1 and 12 depend on `SAIF.NetCore.ServiceExtensions`† (authorization handler and `DynamicErrorHandler`), and step 11 uses `IUnitOfWorkFactory` from `SAIF.NetCore.NHibernate`†, both retrieved via the Legends CLI RAG service and executed within this operation.

## Process flow

The following diagram visualizes this operation's control flow.

```mermaid
flowchart TD
    A["Authorize DBConnAPIRetrieveResource"] -->|"Denied"| Z["Authorization denial, status not visible in source"]
    A --> B["Audit log, build cache key and secure-setting key"]
    B --> T["Read Environment API template (every request)"]
    T -->|"Template missing"| X["Unhandled/package error, status not visible in source"]
    T --> C{"Key in cache?"}
    C -->|"Yes"| R["Return cached string, 200"]
    C -->|"No"| D{"Secure setting exists?"}
    D -->|"No"| E["404 ConnectionManagerException"]
    D -->|"Yes"| F["Decrypt credential, split at first pipe"]
    F -->|"No pipe"| X2["Unhandled/package error, status not visible in source"]
    F --> G["Compose string"]
    G --> H["Probe SELECT 1, up to 4 attempts"]
    H -->|"Valid"| I["Add to cache and return 200"]
    H -->|"Permanent error or attempts exhausted"| J["Map to 400* (inferred) or 500"]
```

## Transaction and side effects

| Aspect | Finding |
| --- | --- |
| Unit of work | `IUnitOfWorkFactory` (`SAIF.NetCore.NHibernate`), a throw-away unit of work per probe attempt; `Begin()` is never called, so there is no explicit transaction |
| Commit point | None |
| Records written | None; the probe reads a constant ([data model](../data-model.md)) |
| Downstream calls | SAIF Environment API through `IEnvironmentClient` (template, existence check, secure read); the caller-named SQL Server through the probe |
| Repeat-call behavior | A repeat call for an identical key reads the template again and is then served from the cache, skipping the secure read and probe; the cache is never invalidated ([BR-DBC-004](../business-rules.md#br-dbc-004-cache-entries-never-expire)). Each distinct key leaves one session factory in memory for the process lifetime† |

## Logging

| Surface | Written content |
| --- | --- |
| `ISaifLogService` (Info) | Audit entry per request: key parts, `UserId`, `AuthenticationScheme`, timestamp ([BR-DBC-022](../business-rules.md#br-dbc-022-retrieval-requests-are-audit-logged)); a validation success entry with the attempt count |
| `ISaifLogService` (Error) | Each failed probe attempt with the exception and message |

## Error handling

- Secure setting missing: 404 from `ConnectionManagerException`, rendered by package middleware (`DynamicErrorHandler`†); the message names the key and track
- Invalid credentials, database unavailable, cursor exhaustion, network interruption, probe returned false or any other failure: 500 from `ConnectionManagerException` through the same middleware; the message can include the raw provider text
- Missing pipe in the credential or missing template: unhandled exceptions handled by package middleware; the status is not visible in source**
- Authorization failure: produced by the package authorization handler†; the status is not visible in source**
- There are no `[ProducesResponseType]` attributes, so 400, 404 and 500 are undeclared in the OpenAPI document

## Related documentation
- [API index](../index.md)
- [Overview](../overview.md)
- [Architecture](../architecture.md)
- [Hosting pipeline](../hosting-pipeline.md)
- [Endpoints](../endpoints.md)
- [Authentication and authorization](../authentication-authorization.md)
- [Dependencies](../dependencies.md)
- [Configuration](../configuration.md)
- [Operations and processes](../operations-processes.md)
- [Business rules](../business-rules.md)
- [Data model](../data-model.md)
- [Operational runbook](../operational-runbook.md)
- [Tech debt](../tech-debt.md)

> **Note:** Statements marked with `†` are derived from dependency library
> documentation via the Legends CLI. Statements marked with `*` were inferred
> from code structure or contextual evidence. Statements marked with `**` could
> not be confirmed; treat as unknown until verified.

## Sources
- DBConnectionAPI/DBConnectionAPI/Controllers/ConnectionsController.cs
- DBConnectionAPI/DBConnectionAPI/Services/ConnectionManager.cs
- DBConnectionAPI/DBConnectionAPI/Services/IConnectionManager.cs
- DBConnectionAPI/DBConnectionAPI/Exceptions/ConnectionManagerException.cs
- DBConnectionAPI/DBConnectionAPI/EnvironmentKeys.cs